package com.wnzt.baszh.core.base;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Projections;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import com.wnzt.baszh.util.common.ConstantsUtils;
import com.wnzt.baszh.util.common.GlobalStatic;

/** 
 * 通用数据库接口实�?
 * 
 * @version [版本�? 2013-7-11 上午10:43:22]
 */
@Component("commonDao")
public class CommonDaoImpl implements ICommonDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    @Qualifier("sessionFactory")
    private SessionFactory sessionFactory;

    /**
     * 获取当前数据库session对象
     * 
     * @return [参数说明]
     * @return Session
     * @exception throws [违例类型] [违例说明]
     */
    public Session getCurrentSession() {
        // 事务必须是开启的(Required)，否则获取不�?
        return this.sessionFactory.getCurrentSession();
    }

    /**
     * @param entity
     */
    @Override
    public void delete(BaseModel entity) {
        if (null != entity) {
            this.getCurrentSession().delete(entity);
        }
    }

    /**
     * @param entity
     */
    @Override
    public void update(BaseModel entity) {
        this.getCurrentSession().update(entity);
    }

    /**
     * @param entityList
     */
    @SuppressWarnings("unchecked")
    @Override
    public void saveOrUpdateList(List<?> entityList) {
        if (ConstantsUtils.isNotEmpty(entityList)) {
            List<BaseModel> list = (List<BaseModel>) entityList;
            for (BaseModel t : list) {
                this.saveOrUpdate(t);
            }
        }
    }

    /**
     * @param entity
     */
    @Override
    public void merge(BaseModel entity) {
        this.getCurrentSession().merge(entity);
    }

    /**
     * @param id
     */
    @Override
    public void deleteById(Class<?> c, Serializable id) {
        this.getCurrentSession().delete(this.get(c, id));
    }

    /**
     * @param entityList
     */
    @SuppressWarnings("unchecked")
    @Override
    public void deleteList(List<?> entityList) {
        if (ConstantsUtils.isNotEmpty(entityList)) {
            List<BaseModel> list = (List<BaseModel>) entityList;
            for (BaseModel t : list) {
                this.delete(t);
            }
        }
    }

    /**
     * @param entity
     * @return
     */
    @Override
    public Serializable save(BaseModel entity) {
        if (null == entity.getStatusCode())
            entity.setStatusCode(Integer.valueOf(GlobalStatic.INT_VALUE_0));
        if (null == entity.getDeleteFlag())
            entity.setDeleteFlag(Integer.valueOf(GlobalStatic.INT_VALUE_0));
        if (null == entity.getCreatedDtm())
            entity.setCreatedDtm(new Date());
        if (null == entity.getLastUserId()){            
            if(RequestContextHolderUtils.getSession() != null){
                if(RequestContextHolderUtils.getSession().getAttribute(GlobalStatic.USER_SESSION_ID) != null){
                    entity.setLastUserId(RequestContextHolderUtils
                            .getSession().getAttribute(GlobalStatic.USER_SESSION_ID)
                            .toString());
                }else{
                    entity.setLastUserId("-1");
                }
            }else{
                entity.setLastUserId("-1");
            }
        }
        return this.getCurrentSession().save(entity);
    }

    /**
     * @param entity
     */
    @Override
    public void saveOrUpdate(BaseModel entity) {
        this.getCurrentSession().saveOrUpdate(entity);
    }

    /**
     * @param hql
     * @param params
     * @return
     */
    @Override
    public int executeHql(String hql, Object... params) {
        Query query = this.getCurrentSession().createQuery(hql);
        if (null != params && params.length > 0) {
            for (int i = 0; i < params.length; i++) {
                query.setParameter(i, params[i]);
            }
        }
        return query.executeUpdate();
    }

    /**
     * @param sql
     * @param values
     */
    @Override
    public void executeSQL(String sql, Object... values) {
        SQLQuery sqlQuery = this.getCurrentSession().createSQLQuery(sql);
        if (values != null && values.length > 0) {
            for (int i = 0; i < values.length; i++) {
                sqlQuery.setParameter(i, values[i]);
            }
        }
        sqlQuery.executeUpdate();
    }

    /**
     * @param hql
     * @param params
     * @return
     */
    @Override
    public List<?> findByHQL(String hql, Object... params) {
        Query query = this.getCurrentSession().createQuery(hql);
        if (null != params && params.length > 0) {
            for (int i = 0; i < params.length; i++) {
                query.setParameter(i, params[i]);
            }
        }
        return query.list();
    }

    /**
     * @param sql
     * @param params
     * @return
     */
    @Override
    public List<?> findBySQL(String sql, Object... params) {
        SQLQuery query = this.getCurrentSession().createSQLQuery(sql);
        if (null != params && params.length > 0) {
            for (int i = 0; i < params.length; i++) {
                query.setParameter(i, params[i]);
            }
        }
        return query.list();
    }

    /**
     * @param detachedCriteria
     * @return
     */
    @Override
    public List<?> findByQBC(DetachedCriteria detachedCriteria) {
        return detachedCriteria.getExecutableCriteria(this.getCurrentSession())
                .list();
    }

    /**
     * @param queryObject
     * @return
     */
    @Override
    public Page findPageByHQL(QueryObject queryObject) {
        final String hql = queryObject.getQueryString();
        final Object[] params = queryObject.getValues();
        final int curPage = queryObject.getCurPage();
        final int pageSize = queryObject.getPageSize();
        Query query = this.getCurrentSession().createQuery(hql);
        if (null != params && params.length > 0) {
            for (int i = 0; i < params.length; i++) {
                query.setParameter(i, params[i]);
            }
        }
        Long totals = getCountByHQL(queryObject);
        query.setFirstResult(getFirstResult(curPage, pageSize));
        query.setMaxResults(pageSize);
        List<?> list = query.list();
        Page page = new Page();
        page.setRows(list);
        page.setTotal(totals.longValue());
        return page;
    }

    /**
     * 设置页码参数
     * 
     * @param curPage
     * @param pageSize
     * @return [参数说明]
     * @return int
     * @exception throws [违例类型] [违例说明]
     */
    private int getFirstResult(int curPage, int pageSize) {
        if (curPage < 1) {
            curPage = 1;
        }
        if (pageSize < 1) {
            pageSize = 10;
        }
        return (curPage - 1) * pageSize;
    }

    /**
     * @param queryObject
     * @return
     */
    @Override
    public Page findPageBySQL(QueryObject queryObject) {
        final String sql = queryObject.getQueryString();
        final Object[] params = queryObject.getValues();
        final int curPage = queryObject.getCurPage();
        final int pageSize = queryObject.getPageSize();

        Query query = this.getCurrentSession().createSQLQuery(sql);
        if (null != params && params.length > 0) {
            for (int i = 0; i < params.length; i++) {
                query.setParameter(i, params[i]);
            }
        }
        Long totals = getCountBySQL(queryObject);
        query.setFirstResult(getFirstResult(curPage, pageSize));
        query.setMaxResults(pageSize);
        List<?> list = query.list();
        Page page = new Page();
        page.setRows(list);
        page.setTotal(totals);
        return page;
    }

    /**
     * @param queryObject
     * @return
     */
    @Override
    public Page findPageByQBC(QueryObject queryObject) {
        DetachedCriteria detachedCriteria = queryObject.getDetachedCriteria();
        int curPage = queryObject.getCurPage();
        int pageSize = queryObject.getPageSize();

        Criteria criteria = detachedCriteria.getExecutableCriteria(this
                .getCurrentSession());
        Object o = criteria.setProjection(Projections.rowCount())
                .uniqueResult();
        Long totals = new Long(0);
        if (null != o) {
            totals = Long.valueOf(o.toString());
        }

        criteria.setProjection(null);
        criteria.setFirstResult(getFirstResult(curPage, pageSize));
        criteria.setMaxResults(pageSize);
        Page page = new Page();
        page.setRows(criteria.list());
        page.setTotal(totals);
        return page;
    }

    /**
     * @param queryType
     * @param queryObject
     * @return
     */
    @Override
    public Page findPage(int queryType, QueryObject queryObject) {
        if (GlobalStatic.QUERY_TYPE_HQL == queryType) {
            return this.findPageByHQL(queryObject);
        } else if (GlobalStatic.QUERY_TYPE_SQL == queryType) {
            return this.findPageBySQL(queryObject);
        } else if (GlobalStatic.QUERY_TYPE_QBC == queryType) {
            return this.findPageByQBC(queryObject);
        }
        return null;
    }

    /**
     * @param id
     * @return
     */
    @Override
    public BaseModel get(Class<?> c, Serializable id) {
        return (BaseModel) this.getCurrentSession().get(c, id);
    }

    /**
     * 根据主键查询是否存在对应数据
     * 
     * @param id
     * @return true:存在 false:不存�?
     * @return boolean
     * @exception throws [违例类型] [违例说明]
     */
    public boolean exists(Class<?> c, Serializable id) {
        return get(c, id) != null;
    }

    /**
     * @param detachedCriteria
     * @return
     */
    @Override
    public BaseModel find(DetachedCriteria detachedCriteria) {
        return (BaseModel) detachedCriteria.getExecutableCriteria(
                getCurrentSession()).uniqueResult();
    }

    /**
     * @param hql
     * @param params
     * @return
     */
    @Override
    public BaseModel find(String hql, Object... params) {
        Query query = getCurrentSession().createQuery(hql);
        setParameters(query, params);
        return (BaseModel) query.setMaxResults(1).uniqueResult();
    }

    /**
     * 设置查询条件
     * 
     * @param query
     * @param paramlist
     *            [参数说明]
     * @return void
     * @exception throws [违例类型] [违例说明]
     */
    protected void setParameters(Query query, Object[] paramlist) {
        if (paramlist != null) {
            for (int i = 0; i < paramlist.length; i++) {
                if (paramlist[i] instanceof Date) {
                    query.setTimestamp(i, (Date) paramlist[i]);
                } else {
                    query.setParameter(i, paramlist[i]);
                }
            }
        }
    }

    /**
     * 生成存储过程的方�?
     * 
     * @param procedureName
     *            存储过程的名�?
     * @param length
     *            参数的个�?
     * @return 生成的执行的存储过程
     */
    private String generationExcuteProdure(String procedureName, int length) {
        StringBuilder sql = new StringBuilder();
        sql.append("{Call ");
        sql.append(StringUtils.trim(procedureName));
        if (length > 0) {
            sql.append("(");
            for (int i = 0; i < length; i++) {
                sql.append("?");
                if (i != length - 1) {
                    sql.append(",");
                }
            }
            sql.append(")");
        }
        sql.append("}");
        return sql.toString();
    }

    /**
     * @param queryObject
     * @return
     */
    @Override
    public Long getCountByHQL(QueryObject queryObject) {
        String hql = queryObject.getQueryString();
        final Object[] params = queryObject.getValues();

        int indexfrom = hql.indexOf("from");
        int indexFROM = hql.indexOf("FROM");
        if (indexfrom != -1 && indexFROM != -1) {
            hql = indexfrom > indexFROM ? hql.substring(hql.indexOf("FROM"))
                    : hql.substring(hql.indexOf("from"));
        } else if (indexfrom != -1) {
            hql = hql.substring(hql.indexOf("from"));
        } else {
            hql = hql.substring(hql.indexOf("FROM"));
        }
        String queryString = "select count(*) " + hql;
        Query query = this.getCurrentSession().createQuery(queryString);
        if (params != null && params.length > 0) {
            for (int i = 0; i < params.length; i++) {
                query.setParameter(i, params[i]);
            }
        }
        return (Long) query.uniqueResult();
    }

    /**
     * @param detachedCriteria
     * @return
     */
    @Override
    public Long getCountByQBC(DetachedCriteria detachedCriteria) {
        Criteria criteria = detachedCriteria.getExecutableCriteria(this
                .getCurrentSession());
        Object o = criteria.setProjection(Projections.rowCount())
                .uniqueResult();
        return Long.valueOf(o.toString());
    }

    /**
     * @param queryObject
     * @return
     */
    @Override
    public Long getCountBySQL(QueryObject queryObject) {
        String sql = queryObject.getQueryString();
        final Object[] params = queryObject.getValues();
        final String queryString = "select count(*) from (" + sql + ") t";
        Query query = this.getCurrentSession().createSQLQuery(queryString);
        if (params != null && params.length > 0) {
            for (int i = 0; i < params.length; i++) {
                query.setParameter(i, params[i]);
            }
        }
        return Long.valueOf(query.uniqueResult().toString());
    }

    /**
     * 
     */
    @Override
    public void flush() {
        getCurrentSession().flush();
    }

    /**
     * 
     */
    @Override
    public void clear() {
        getCurrentSession().clear();
    }

    /**
     * @param queryObject
     * @param map
     * @return
     */
    @Override
    public List<Object> findListByProc(QueryObject queryObject,
            Map<Integer, Integer> map) throws Exception {
        Object[] params = queryObject.getValues();
        List<Object> list = new ArrayList<Object>();
        int length = null == params ? 0 : params.length;
        Connection connection = this.jdbcTemplate.getDataSource().getConnection();
        CallableStatement stmt = null;
        if (null != map) {
            stmt = connection.prepareCall(generationExcuteProdure(queryObject.getQueryString(), length + map.size()));
            Set<Map.Entry<Integer, Integer>> set = map.entrySet();
            for (Iterator<Map.Entry<Integer, Integer>> it = set.iterator(); it.hasNext();) {
                Map.Entry<Integer, Integer> entry = (Map.Entry<Integer, Integer>) it.next();
                // 设置输出参数
                stmt.registerOutParameter(entry.getKey().intValue(), entry.getValue().intValue());
            }
            setStringParams(params, stmt);
            stmt.execute();

            for (Iterator<Map.Entry<Integer, Integer>> it = set.iterator(); it.hasNext();) {
                Map.Entry<Integer, Integer> entry = (Map.Entry<Integer, Integer>) it.next();
                list.add(stmt.getObject(entry.getKey().intValue()));
            }
        } else {
            stmt = connection.prepareCall(generationExcuteProdure(queryObject.getQueryString(), length));
            if (length > 0) {
                setStringParams(params, stmt);
            }
            stmt.execute();
        }
        stmt.close();
        connection.close();
        return list;
    }

    /**
     * sql参数设置
     * 
     * @param params
     * @param pstmt
     * 
     * @throws SQLException
     */
    private void setStringParams(Object[] params, PreparedStatement pstmt)
            throws SQLException {
        if (null != params) {
            if (params.length > 0) {
                for (int i = 0; i < params.length; i++) {
                    pstmt.setObject(i + 1, params[i]);
                }
            }
        }
    }

    /**
     * @param tableName
     * @param isBigInt
     * @return
     */
    @Override
    public Long getIntID(String tableName, boolean isBigInt) {
        Map<Integer, Integer> outMap = new HashMap<Integer, Integer>();
        // 输出参数:int(32)
        outMap.put(3, Types.INTEGER);
        // 输出参数:bigInt(64)
        outMap.put(4, Types.BIGINT);
        int inType = isBigInt ? GlobalStatic.INT_VALUE_1
                : GlobalStatic.INT_VALUE_0;
        List<?> list = null;
        try {
            list = this.findListByProc(new QueryObject(
                    GlobalStatic.ACT_PROC_GET_INDEX_PROC, new Object[] {
                            tableName, inType }), outMap);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (ConstantsUtils.isNotEmpty(list)) {
            return isBigInt ? Long.valueOf(list.get(1).toString()) : Long
                    .valueOf(list.get(0).toString());
        }
        return null;
    }
    
    /**
     * 查询传入的科室统一科号下的所有子科室号
     * 如果该统一科号下没有子科室或子科室为空，则返回空字符串
     * @param depart 科室统一科号
     * @return 子科室科号拼接字符串
     */
    public String findChildrenDepartments(String depart) {
        String numStr = "";
        if (ConstantsUtils.isNotBank(depart)) {
            String sql = "select fcal from tworkroom where ftykh='" + depart + "'";
            List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql);
            if (!list.isEmpty()) {
                Map<String, Object> map = list.get(0);
                if (map.containsKey("fcal")) {
                    String departNum = (String) map.get("fcal");
                    if (ConstantsUtils.isNotBank(departNum)) {
                        numStr = departNum.replaceAll(",", "','");
                    }
                }
            }
        }
        return numStr;
    }
}

